﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace QLDAOL.Models
{
    public class IEQLDAOLRepository : IQLDAOLRepository
    {
        public QLDAOnLineEntities db = new QLDAOnLineEntities();

        public void savecommit()
        {
            db.SaveChanges();
        }
        public USER ValidateUser(string loginName, string password)
        {
            return db.USERs.FirstOrDefault(u => u.UserName == loginName && u.Password == password);
        }
        public USER getUserByUsername(string user_name)
        {
            return db.USERs.Where(m => m.UserName == user_name).FirstOrDefault();
        }
        public USER getUserByUserid(int user_id)
        {
            return db.USERs.Where(m => m.ID ==user_id).FirstOrDefault();
        }
        public void addUser(USER newUser)
        {
            db.AddToUSERs(newUser);
            savecommit();
        }
        public int getUserId(string username)
        {
            return db.USERs.Where(m => m.UserName== username).FirstOrDefault().ID;
        }
        #region Mail

        public IEnumerable<MAIL> getMailByUser(int user)
        {
            return db.MAILs.Where(m => m.ID_UserReceived == user).OrderByDescending(m => m.Date_Created);
        }
        public IEnumerable<MAIL> getSentMailByUser(int id)
        {
            return db.MAILs.Where(m => m.ID_UserCreated == id).OrderByDescending(m => m.Date_Created);
        }
        public MAIL getMailByMailid(int Mail_id)
        {
            return db.MAILs.Where(m => m.ID == Mail_id).FirstOrDefault();
        }
        public void addMail(MAIL newMail)
        {
            db.AddToMAILs(newMail);
            savecommit();
        }
        #endregion

        #region danhmuc
        public IEnumerable<DM_PROJECT_LANGUAGE> getlstProjectLang()
        {
            var res = from p in db.DM_PROJECT_LANGUAGE select p;
            return res.AsEnumerable();
        }
        #endregion

        #region project
        public IEnumerable<PROJECT> getlstPJSearch(string pjName, int pjLag)
        {
            IEnumerable<PROJECT> res;
            if (pjName != null && pjLag != 0)
                res = from p in db.PROJECTs where p.ProjectName.Contains(pjName) && p.ID_ProjectLanguage == pjLag select p;
            else
                if (pjName != null)
                    res = from p in db.PROJECTs where p.ProjectName.Contains(pjName) select p;
                else
                    res = from p in db.PROJECTs where p.ID_ProjectLanguage == pjLag select p;
            return res.AsEnumerable();
        }

        public bool checkPJAccess(int pjID, int userID)
        {
            var res = from p in db.USER_PROJECT_ROLE
                      where p.ID_Project == pjID && p.ID_User == userID
                      where p.DM_ROLE.ID >= 4 && p.DM_ROLE.Type==1 //Danh
                      select p;
            if (res.Count() >= 1)
                return true;
            return false;
        }
        #endregion

        #region user
        public IEnumerable<USER_PROJECT_ROLE> getlstUserRoleByPJID(int pjID)
        {
            var res = from u in db.USERs join p in db.USER_PROJECT_ROLE on u.ID equals p.ID_User where p.ID_Project == pjID && p.ID_ProjectRole >= 4 && p.DM_ROLE.Type == 1 select p;
            return res;

        }
        public IEnumerable<USER> getlstUserByPJID(int pjID)
        {
            var res= from u in db.USERs join p in db.USER_PROJECT_ROLE on u.ID equals p.ID_User where p.ID_Project == pjID && p.ID_ProjectRole>=4 && p.DM_ROLE.Type==1 select u;
            return res;

        }
        public IEnumerable<USER> getlstUserOnlineByPJID(int pjID)
        {
            var res = from u in db.USERs join p in db.USER_PROJECT_ROLE on u.ID equals p.ID_User where p.ID_Project == pjID && p.ID_ProjectRole >= 4 && p.DM_ROLE.Type == 1 && u.IsOnline==true select u;
            return res;
        }
        public IEnumerable<USER> getlst6UserByPJID(int pjID)
        {
            var res = getlstUserOnlineByPJID(pjID);
            if (res.Count() >= 3)
                return res;
            res = getlstUserByPJID(pjID);
            //int i = 1;
            //for (i=1; i * 3 <= res.Count() && i<4; i++) { }
            //    res = res.Take((i-1)*3);
            return res.Take(9);

        }
        public void addUserProject(string email, int role, int id)
        {
            var u = from p in db.USERs where p.Email == email select p;
            USER_PROJECT_ROLE res = new USER_PROJECT_ROLE();
            res.ID_User = u.First().ID;
            res.ID_ProjectRole = role;
            res.ID_Project = id;
            db.USER_PROJECT_ROLE.AddObject(res);
            db.SaveChanges();
        }
        #endregion

        #region news_QA
        public IEnumerable<NEWS_QUESTIONS> getlstLatestNewsQA(int type, int ID_PJ)
        {
            var res= (from p in db.NEWS_QUESTIONS where p.Active==true && p.Type==type && p.ID_Project==ID_PJ orderby p.Date_Created descending select p).Take(5);
            return res.AsEnumerable();
        }
        public IEnumerable<NEWS_QUESTIONS> getlstNewsQA(int type, int ID_PJ)
        {
            var res = from p in db.NEWS_QUESTIONS where p.Active == true && p.Type == type && p.ID_Project==ID_PJ  select p;
            return res.AsEnumerable();
        }
        public NEWS_QUESTIONS getInfoByID(int id)
        {
            var res = from p in db.NEWS_QUESTIONS where p.ID == id && p.Active == true select p;
            return res.First();
        }
        public void addNewsQA(NEWS_QUESTIONS n)
        {
            db.NEWS_QUESTIONS.AddObject(n);
            db.SaveChanges();
        }
        public void delNewsQA(int id)
        {
            var res = (from p in db.NEWS_QUESTIONS where p.Active == true && p.ID == id select p).First();
            res.Active = false;
            db.SaveChanges();
        }
        #endregion

        #region task
        public IEnumerable<DIVISION_TASK> getlstLatestTask(int ID_PJ)
        {
            var res = from p in db.DIVISION_TASK where p.TASK.ID_Project==ID_PJ select p;
            return res;
        }
        public IEnumerable<DIVISION_TASK> getlstTask(int ID_PJ)
        {
            var res = from p in db.DIVISION_TASK where p.TASK.ID_Project == ID_PJ select p;
            return res;
        }
        public IEnumerable<DIVISION_TASK> getlstTask(int userid, int ID_PJ)
        {
            var res = from p in db.DIVISION_TASK where p.TASK.ID_Project == ID_PJ && p.ID_User == userid select p;
            return res;
        }
        public IEnumerable<DIVISION_TASK> getAllTaskByUSer(int id_user)
        {
            var res = from p in db.DIVISION_TASK where p.ID_User == id_user select p;
            return res;
        }
        public IEnumerable<DIVISION_TASK> getAllTaskByUSer_PJ(int id_user, int ID_PJ)
        {
            var res = from p in db.DIVISION_TASK where p.ID_User == id_user && p.TASK.ID_Project == ID_PJ select p;
            return res;
        }
        public void  addTask(string task,int userid,string start,string dead,int id,int status)
        { 
        TASK ta= new TASK();
        ta.ID_Project = id;
        ta.ID_State = status;
        ta.Task1 = task;
        ta.Active = true;
        db.TASKs.AddObject(ta);
        db.SaveChanges();
        var res = (from p in db.TASKs where p.Task1 == task select p).First();
        DIVISION_TASK d = new DIVISION_TASK();
        d.ID_Task = res.ID;
        d.ID_User = userid;
        d.Date_Start = DateTime.Parse(start);
        d.Deadline =DateTime.Parse(dead);
        db.DIVISION_TASK.AddObject(d);
        db.SaveChanges();
        }
        public void editTask(int taskID, string task, int userid, string start, string dead, int id, int status)
        {
            var res = (from p in db.DIVISION_TASK where p.ID == taskID select p).First();

            res.TASK.ID_Project = id;
            res.TASK.ID_State = status;
            res.TASK.Task1 = task;
            res.TASK.Active = true;
            db.SaveChanges();

            res.ID_Task = res.ID;
            res.ID_User = userid;
            res.Date_Start = DateTime.Parse(start);
            res.Deadline = DateTime.Parse(dead);
            db.SaveChanges();
        }
        public void delTask(int id) { return; }
        public DIVISION_TASK gettask(int taskID)
        {
            var task = from p in db.DIVISION_TASK where p.ID_Task == taskID select p;
            return task.First();
        }
        public IEnumerable<DM_State> getlstTaskState()
        {
            var res = from p in db.DM_State where p.Active == true select p;
            return res;
        }
        #endregion

        #region bug
        #endregion

        public void temp()
        {
            var res = from p in db.NEWS_QUESTIONS select p;
            foreach (var temp in res)
            {
                temp.Date_Created = DateTime.Now;
            }
            db.SaveChanges();
        }
        #region download
        public IEnumerable<DOWNLOAD> getAllDownload(int pjid)
        {
            return db.DOWNLOADs.Where(m=>m.Project_Id==pjid).OrderByDescending(d => d.Date);
        }
        public DOWNLOAD getDownloadById(int id)
        {
            return db.DOWNLOADs.FirstOrDefault(d => d.ID == id);
        }
        public void addtoDownload(DOWNLOAD newdl)
        {
            db.AddToDOWNLOADs(newdl);
            savecommit();
        }
        public void deletedownload(DOWNLOAD del)
        {
            db.DOWNLOADs.DeleteObject(del);
            savecommit();
        }
        public IEnumerable<DOWNLOAD> getAllDownloadbyuser(string username)
        {
            return db.DOWNLOADs.Where(m => m.UserName == username).OrderByDescending(d => d.Date);
        }
        #endregion

        public IEnumerable<PROJECT_SUPPORTER> getlstSupporter(int PjID)
        {
            var re = from p in db.PROJECT_SUPPORTER where p.ID_Project == PjID select p;
            return re;
        }
        #region Danhmuc
        public IEnumerable<DM_ROLE> getlstPJRole()
        {
            var res = from p in db.DM_ROLE where p.Active==true && p.ID>=4 select p;
            return res;
        }
        #endregion
        #region pjchat
        public void addchat(PJ_CHAT newChat)
        {
            db.AddToPJ_CHAT(newChat);
            savecommit();
        }
        public IEnumerable<PJ_CHAT> getchatbyPjID(int PjID)
        {

            var p = db.PJ_CHAT.Where(m => m.Pj_id == PjID).OrderBy(m => m.Date);
            return p.Skip(Math.Max(0, p.Count() - 10)).Take(10);
        }
        #endregion
        #region PJcalendar
        public IEnumerable<PJ_CALENDAR> getAllPJCalendar(int pjid)
        {
            return db.PJ_CALENDAR.Where(m => m.Pj_id == pjid);
        }
        public void insertPjCalendar(PJ_CALENDAR P)
        {
            db.AddToPJ_CALENDAR(P);
            savecommit();
        }
        public PJ_CALENDAR getPjCalendarByID(int id)
        {
            return db.PJ_CALENDAR.Where(m => m.ID == id).FirstOrDefault();
        }
        public void deletePjCalendar(PJ_CALENDAR P)
        {
            db.PJ_CALENDAR.DeleteObject(P);
            savecommit();
        }
        public void UpdatePjCalendar(PJ_CALENDAR P)
        {
            PJ_CALENDAR up = getPjCalendarByID(P.ID);
            up.Start_date = P.Start_date;
            up.End_date = P.End_date;
            up.Description = P.Description;
            up.Pj_id = P.Pj_id;
            savecommit();
        }
        public void inserttopjuser(int pjid, PJ_CALENDAR p)
        {
            var record = getuserpjrolebypjid(pjid);
            foreach (var a in record)
            {
                USER_CALENDAR newca = new USER_CALENDAR
                {
                    User_id=(int)a.ID_User,
                    End_date= p.End_date,
                    Start_date=p.Start_date,
                    Description= p.Description,
                    Pj_Calendar=p.ID
                };
                db.AddToUSER_CALENDAR(newca);
            }
            savecommit();

        }
        public void updatetopjuser(int pjid, PJ_CALENDAR p)
        {
            var record = getUserCalendarByPjCalendarId(p.ID);
            foreach (var a in record)
            {
                USER_CALENDAR update = getUserCalendarByID(a.ID);
                update.Description = p.Description;
                update.End_date = p.End_date;
                update.Start_date = p.Start_date;     
            }
            savecommit();
        }
        public void deletetopjuser(int pjid, PJ_CALENDAR p)
        {
            var record = getUserCalendarByPjCalendarId(p.ID);
            foreach (var a in record)
            {
                USER_CALENDAR delca = getUserCalendarByID(a.ID);
                db.USER_CALENDAR.DeleteObject(delca);
            }
            savecommit();

        }
        #endregion
        #region USercalendar
        public IEnumerable<USER_CALENDAR> getAllUserCalendar(int userid)
        {
            return db.USER_CALENDAR.Where(m => m.User_id == userid);
        }
        public void insertUserCalendar(USER_CALENDAR P)
        {
            db.AddToUSER_CALENDAR(P);
            savecommit();
        }
        public USER_CALENDAR getUserCalendarByID(int id)
        {
            return db.USER_CALENDAR.Where(m => m.ID == id).FirstOrDefault();
        }
        public void deleteUserCalendar(USER_CALENDAR P)
        {
            db.USER_CALENDAR.DeleteObject(P);
            savecommit();
        }
        public void UpdateUserCalendar(USER_CALENDAR P)
        {
            USER_CALENDAR up = getUserCalendarByID(P.ID);
            up.Start_date = P.Start_date;
            up.End_date = P.End_date;
            up.Description = P.Description;
            up.User_id = P.User_id;
            savecommit();
        }
        public IEnumerable<USER_CALENDAR> getUserCalendarByPjCalendarId(int id)
        {
            return db.USER_CALENDAR.Where(m => m.Pj_Calendar == id);
        }
        #endregion
        #region user_project_role

        public IEnumerable<USER_PROJECT_ROLE> getuserpjrolebypjid(int pjid)
        {
            return db.USER_PROJECT_ROLE.Where(m => m.ID_Project == pjid);
        }
        public bool ispm(int pjid, int userid)
        {
            var kq = db.USER_PROJECT_ROLE.Where(m => m.ID_User == userid && m.ID_Project == pjid).FirstOrDefault();
            if (kq.ID_ProjectRole >= 5 && kq.DM_ROLE.Type == 1)
                return true;
            return false;
        }
        #endregion
    }
}